package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select bp.name name,sum(o.final_total_amount) value from order_info o left join base_province bp\n" +
            "    on o.province_id = bp.id group by bp.name")
    List<Vo> getList1();
    @Select("select bt.name name,sum(o.final_total_amount)value from order_info o left join order_detail od on o.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join base_trademark bt on si.tm_id = bt.id\n" +
            "group by bt.name")
    List<Vo> getList2();
    @Select("select bc1.name name,sum(o.final_total_amount)value from order_info o left join order_detail od on o.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join base_category3 b on si.category3_id = b.id\n" +
            "left join base_category2 c on b.category2_id = c.id\n" +
            "left join base_category1 bc1 on c.category1_id = bc1.id\n" +
            "group by bc1.name")
    List<Vo> getList3();
    @Select("select '用户数(人)'type,sum(case when timestampdiff(year,create_time,now())>1 then 1 else 0 end)olduser,\n" +
            "       sum(case when timestampdiff(year,create_time,now())<1 then 1 else 0 end)newuser\n" +
            "from user_info")
    List<Vo1> getList4();
}
